from typing import List

from sqlalchemy import exc, or_, and_
from sqlalchemy.orm import Session, joinedload, with_loader_criteria

from model.TeachingModel import AttendanceModel, StudentModel
from schema.QuerySchema import QueryBase, AttendanceQuery, AttendanceDateIntervalQuery
from schema.TeachingSchema import AttendanceInfo
from util.ErrorCode import ErrorCode
from util.LogHelper import Log


def batch_add_attendance(attendance_list: List, db: Session):
    try:
        for attendance in attendance_list:
            db.add(attendance)
        db.commit()
        return {'code': ErrorCode.SUCCESS.value, 'message': 'success'}
    except exc.SQLAlchemyError as e:
        db.rollback()
        Log.getlogger().error(f'batch_add_attendance failed! message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


def mod_attendance(attendanceInfo: AttendanceInfo, db: Session):
    attendanceModel = db.query(AttendanceModel).filter(
        AttendanceModel.id == attendanceInfo.id).one_or_none()
    if attendanceModel is not None:
        attendanceModel.is_normal = attendanceInfo.is_normal
        if attendanceInfo.abnormal_type != 0:
            attendanceModel.abnormal_type = attendanceInfo.abnormal_type
        if attendanceInfo.remark is not None:
            attendanceModel.remark = attendanceInfo.remark if len(
                attendanceInfo.remark) != 0 else attendanceModel.remark

        try:
            db.commit()
            return {'code': ErrorCode.SUCCESS.value, 'message': 'success'}
        except exc.SQLAlchemyError as e:
            Log.getlogger().error(f'mod_attendance failed:{attendanceInfo} message: {e}')
            return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}
    else:
        Log.getlogger().error(f'id:{attendanceInfo.id} is not exists!')
        return {'code': ErrorCode.DATABASE_RECORD_NOT_FOUND.value, 'message': 'record not exists!'}


# attend_date、student_id、is_normal、abnormal_type查询
def query_attendance_by_field(attendanceQuery: AttendanceQuery, db: Session):
    try:
        if attendanceQuery.attend_date is not None and len(attendanceQuery.attend_date) == 0:
            attendanceQuery.attend_date = None

        total_count = db.query(AttendanceModel).filter(and_(
            or_(AttendanceModel.attend_date == attendanceQuery.attend_date, attendanceQuery.attend_date is None),
            or_(AttendanceModel.student_id == attendanceQuery.student_id, attendanceQuery.student_id == 0),
            or_(AttendanceModel.is_normal == attendanceQuery.is_normal, attendanceQuery.is_normal == None),
            or_(AttendanceModel.abnormal_type == attendanceQuery.abnormal_type, attendanceQuery.abnormal_type == 0)
        )).count()
        total_pages = (total_count + attendanceQuery.query_base.limit - 1) // attendanceQuery.query_base.limit

        result = db.query(AttendanceModel).filter(and_(
            or_(AttendanceModel.attend_date == attendanceQuery.attend_date, attendanceQuery.attend_date is None),
            or_(AttendanceModel.student_id == attendanceQuery.student_id, attendanceQuery.student_id == 0),
            or_(AttendanceModel.is_normal == attendanceQuery.is_normal, attendanceQuery.is_normal == None),
            or_(AttendanceModel.abnormal_type == attendanceQuery.abnormal_type, attendanceQuery.abnormal_type == 0)
        )).offset((attendanceQuery.query_base.skip - 1) * attendanceQuery.query_base.limit).limit(
            attendanceQuery.query_base.limit).all()

        return {'code': ErrorCode.SUCCESS.value, 'message': 'success', 'total_count': total_count,
                'total_pages': total_pages, 'data': result}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(f'query_attendance_by_field failed! {attendanceQuery}  message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}



def query_attendance_by_dateInterval(attendanceDateIntervalQuery: AttendanceDateIntervalQuery, db: Session):
    try:
        if attendanceDateIntervalQuery.attend_date_begin is not None and len(attendanceDateIntervalQuery.attend_date_begin) == 0:
            attendanceDateIntervalQuery.attend_date_begin = '19990101'
        if attendanceDateIntervalQuery.attend_date_end is not None and len(
                attendanceDateIntervalQuery.attend_date_end) == 0:
            attendanceDateIntervalQuery.attend_date_end = '29991231'

        total_count = db.query(AttendanceModel).filter(and_(
            AttendanceModel.attend_date.between(attendanceDateIntervalQuery.attend_date_begin,attendanceDateIntervalQuery.attend_date_end),
            AttendanceModel.student_id == attendanceDateIntervalQuery.student_id,
            or_(AttendanceModel.is_normal == attendanceDateIntervalQuery.is_normal, attendanceDateIntervalQuery.is_normal == None),
            or_(AttendanceModel.abnormal_type == attendanceDateIntervalQuery.abnormal_type, attendanceDateIntervalQuery.abnormal_type == 0)
        )).count()
        total_pages = (total_count + attendanceDateIntervalQuery.query_base.limit - 1) // attendanceDateIntervalQuery.query_base.limit

        result = db.query(AttendanceModel).filter(and_(
                AttendanceModel.attend_date.between(attendanceDateIntervalQuery.attend_date_begin,attendanceDateIntervalQuery.attend_date_end),
                AttendanceModel.student_id == attendanceDateIntervalQuery.student_id,
                or_(AttendanceModel.is_normal == attendanceDateIntervalQuery.is_normal, attendanceDateIntervalQuery.is_normal == None),
                or_(AttendanceModel.abnormal_type == attendanceDateIntervalQuery.abnormal_type, attendanceDateIntervalQuery.abnormal_type == 0)
            )).order_by(AttendanceModel.attend_date).offset((attendanceDateIntervalQuery.query_base.skip - 1) * attendanceDateIntervalQuery.query_base.limit).limit(
                attendanceDateIntervalQuery.query_base.limit).all()

        return {'code': ErrorCode.SUCCESS.value, 'message': 'success', 'total_count': total_count,
                'total_pages': total_pages, 'data': result}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(f'query_attendance_by_dateInterval failed! {attendanceDateIntervalQuery}  message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}
def query_attendance_by_class_and_date(attend_date: str, class_id: int, db: Session):
    try:
        result = db.query(AttendanceModel).options(joinedload(AttendanceModel.student_info)).filter(and_(AttendanceModel.attend_date == attend_date, AttendanceModel.class_id == class_id)).all()
        return {'code': ErrorCode.SUCCESS.value, 'message': 'success', 'data': result}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(f'query_attendance_by_class_and_date failed!  message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}


def query_attendance_by_page(query_base: QueryBase, db: Session):
    try:
        total_count = db.query(AttendanceModel).count()
        total_pages = (total_count + query_base.limit - 1) // query_base.limit

        result = db.query(AttendanceModel).offset((query_base.skip - 1) * query_base.limit).limit(
            query_base.limit).all()
        return {'code': ErrorCode.SUCCESS.value, 'message': 'success', 'total_count': total_count,
                'total_pages': total_pages, 'data': result}
    except exc.SQLAlchemyError as e:
        Log.getlogger().error(f'query_attendance_by_page failed!  message: {e}')
        return {'code': ErrorCode.DATABASE_ERROR.value, 'message': 'database error'}
